import sqlite3
import os
import json
from datetime import datetime
from pathlib import Path
from typing import List, Dict, Optional, Tuple
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class LibraryManager:
    """
    管理已下载书籍的SQLite数据库
    """
    
    def __init__(self, db_path: str = "downloads/library.db"):
        """
        初始化数据库管理器
        
        Args:
            db_path: SQLite数据库文件路径
        """
        self.db_path = db_path
        self._init_database()
    
    def _init_database(self):
        """初始化数据库表结构"""
        os.makedirs(os.path.dirname(self.db_path), exist_ok=True)
        
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 创建书籍表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS books (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    book_id TEXT UNIQUE NOT NULL,
                    title TEXT NOT NULL,
                    author TEXT,
                    publisher TEXT,
                    description TEXT,
                    tags TEXT,
                    cover_url TEXT,
                    download_path TEXT NOT NULL,
                    file_size INTEGER,
                    file_format TEXT,
                    download_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    wenku8_url TEXT,
                    metadata_hash TEXT
                )
            """)
            
            # 创建搜索索引
            cursor.execute("""
                CREATE INDEX IF NOT EXISTS idx_books_title ON books(title)
            """)
            cursor.execute("""
                CREATE INDEX IF NOT EXISTS idx_books_author ON books(author)
            """)
            cursor.execute("""
                CREATE INDEX IF NOT EXISTS idx_books_tags ON books(tags)
            """)
            
            conn.commit()
    
    def add_book(self, book_data: Dict) -> bool:
        """
        添加书籍到数据库
        
        Args:
            book_data: 书籍数据字典
            
        Returns:
            bool: 是否成功添加
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                cursor.execute("""
                    INSERT OR REPLACE INTO books (
                        book_id, title, author, publisher, description, 
                        tags, cover_url, download_path, file_size, 
                        file_format, wenku8_url, metadata_hash
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    book_data.get('book_id'),
                    book_data.get('title'),
                    book_data.get('author'),
                    book_data.get('publisher'),
                    book_data.get('description'),
                    json.dumps(book_data.get('tags', []), ensure_ascii=False),
                    book_data.get('cover_url'),
                    book_data.get('download_path'),
                    book_data.get('file_size'),
                    book_data.get('file_format'),
                    book_data.get('wenku8_url'),
                    book_data.get('metadata_hash')
                ))
                
                conn.commit()
                logger.info(f"已添加书籍到数据库: {book_data.get('title')}")
                return True
                
        except sqlite3.Error as e:
            logger.error(f"添加书籍到数据库失败: {e}")
            return False
    
    def search_books(self, query: str = "", filters: Dict = None) -> List[Dict]:
        """
        搜索书籍
        
        Args:
            query: 搜索关键词
            filters: 过滤条件
            
        Returns:
            List[Dict]: 书籍列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                conn.row_factory = sqlite3.Row
                cursor = conn.cursor()
                
                where_clauses = []
                params = []
                
                if query:
                    where_clauses.append("""
                        (title LIKE ? OR author LIKE ? OR tags LIKE ?)
                    """)
                    search_term = f"%{query}%"
                    params.extend([search_term, search_term, search_term])
                
                if filters:
                    if filters.get('author'):
                        where_clauses.append("author = ?")
                        params.append(filters['author'])
                    
                    if filters.get('publisher'):
                        where_clauses.append("publisher = ?")
                        params.append(filters['publisher'])
                
                where_clause = " AND ".join(where_clauses) if where_clauses else "1=1"
                
                cursor.execute(f"""
                    SELECT * FROM books 
                    WHERE {where_clause}
                    ORDER BY download_date DESC
                """, params)
                
                books = []
                for row in cursor.fetchall():
                    book = dict(row)
                    # 解析tags字段
                    if book.get('tags'):
                        book['tags'] = json.loads(book['tags'])
                    books.append(book)
                
                return books
                
        except sqlite3.Error as e:
            logger.error(f"搜索书籍失败: {e}")
            return []
    
    def get_book_by_id(self, book_id: str) -> Optional[Dict]:
        """
        根据ID获取书籍信息
        
        Args:
            book_id: 书籍ID
            
        Returns:
            Optional[Dict]: 书籍信息
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                conn.row_factory = sqlite3.Row
                cursor = conn.cursor()
                
                cursor.execute("""
                    SELECT * FROM books WHERE book_id = ?
                """, (book_id,))
                
                row = cursor.fetchone()
                if row:
                    book = dict(row)
                    if book.get('tags'):
                        book['tags'] = json.loads(book['tags'])
                    return book
                return None
                
        except sqlite3.Error as e:
            logger.error(f"获取书籍信息失败: {e}")
            return None
    
    def update_book_metadata(self, book_id: str, metadata: Dict) -> bool:
        """
        更新书籍元数据
        
        Args:
            book_id: 书籍ID
            metadata: 新的元数据
            
        Returns:
            bool: 是否成功更新
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                cursor.execute("""
                    UPDATE books SET 
                        title = ?, author = ?, publisher = ?, description = ?,
                        tags = ?, cover_url = ?, wenku8_url = ?, 
                        metadata_hash = ?, last_updated = CURRENT_TIMESTAMP
                    WHERE book_id = ?
                """, (
                    metadata.get('title'),
                    metadata.get('author'),
                    metadata.get('publisher'),
                    metadata.get('description'),
                    json.dumps(metadata.get('tags', []), ensure_ascii=False),
                    metadata.get('cover_url'),
                    metadata.get('wenku8_url'),
                    metadata.get('metadata_hash'),
                    book_id
                ))
                
                conn.commit()
                logger.info(f"已更新书籍元数据: {book_id}")
                return True
                
        except sqlite3.Error as e:
            logger.error(f"更新书籍元数据失败: {e}")
            return False
    
    def delete_book(self, book_id: str) -> bool:
        """
        从数据库中删除书籍
        
        Args:
            book_id: 书籍ID
            
        Returns:
            bool: 是否成功删除
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                cursor.execute("""
                    DELETE FROM books WHERE book_id = ?
                """, (book_id,))
                
                conn.commit()
                logger.info(f"已从数据库删除书籍: {book_id}")
                return True
                
        except sqlite3.Error as e:
            logger.error(f"删除书籍失败: {e}")
            return False
    
    def get_statistics(self) -> Dict:
        """
        获取数据库统计信息
        
        Returns:
            Dict: 统计信息
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                stats = {}
                
                # 总书籍数量
                cursor.execute("SELECT COUNT(*) FROM books")
                stats['total_books'] = cursor.fetchone()[0]
                
                # 按作者统计
                cursor.execute("""
                    SELECT author, COUNT(*) as count 
                    FROM books 
                    WHERE author IS NOT NULL 
                    GROUP BY author 
                    ORDER BY count DESC
                """)
                stats['authors'] = dict(cursor.fetchall())
                
                # 按出版社统计
                cursor.execute("""
                    SELECT publisher, COUNT(*) as count 
                    FROM books 
                    WHERE publisher IS NOT NULL 
                    GROUP BY publisher 
                    ORDER BY count DESC
                """)
                stats['publishers'] = dict(cursor.fetchall())
                
                # 文件格式统计
                cursor.execute("""
                    SELECT file_format, COUNT(*) as count 
                    FROM books 
                    WHERE file_format IS NOT NULL 
                    GROUP BY file_format 
                    ORDER BY count DESC
                """)
                stats['formats'] = dict(cursor.fetchall())
                
                # 总文件大小
                cursor.execute("SELECT SUM(file_size) FROM books WHERE file_size IS NOT NULL")
                total_size = cursor.fetchone()[0] or 0
                stats['total_size'] = total_size
                stats['total_size_gb'] = round(total_size / (1024**3), 2)
                
                return stats
                
        except sqlite3.Error as e:
            logger.error(f"获取统计信息失败: {e}")
            return {}


class DownloadScanner:
    """
    扫描下载目录并自动添加到数据库
    """
    
    def __init__(self, download_dir: str = "downloads"):
        """
        初始化扫描器
        
        Args:
            download_dir: 下载目录路径
        """
        self.download_dir = download_dir
        self.library_manager = LibraryManager()
    
    def scan_downloads(self) -> List[Dict]:
        """
        扫描下载目录，返回发现的书籍文件
        支持两种结构：
        1. 直接包含EPUB文件：文件名 = 书名 - 作者.epub
        2. 文件夹结构：文件夹名 = 书名，文件夹内EPUB文件 = 章节名
        
        Returns:
            List[Dict]: 发现的书籍列表
        """
        books_found = []
        
        try:
            # 首先扫描直接包含的EPUB文件
            for root, dirs, files in os.walk(self.download_dir):
                for file in files:
                    if file.endswith('.epub'):
                        file_path = os.path.join(root, file)
                        # 跳过文件夹结构中的章节文件（在子目录中）
                        if os.path.dirname(file_path) == self.download_dir:
                            book_info = self._extract_book_info(file_path)
                            if book_info:
                                books_found.append(book_info)
            
            # 扫描文件夹结构
            for item in os.listdir(self.download_dir):
                item_path = os.path.join(self.download_dir, item)
                if os.path.isdir(item_path):
                    # 检查是否是书籍文件夹
                    book_info = self._scan_book_folder(item_path)
                    if book_info:
                        books_found.append(book_info)
            
            return books_found
            
        except Exception as e:
            logger.error(f"扫描下载目录失败: {e}")
            return []
    
    def _scan_book_folder(self, folder_path: str) -> Optional[Dict]:
        """
        扫描书籍文件夹结构
        
        Args:
            folder_path: 文件夹路径
            
        Returns:
            Optional[Dict]: 书籍信息
        """
        try:
            folder_name = os.path.basename(folder_path)
            
            # 查找文件夹中的EPUB文件
            epub_files = []
            for file in os.listdir(folder_path):
                if file.endswith('.epub') and os.path.isfile(os.path.join(folder_path, file)):
                    epub_files.append(file)
            
            if not epub_files:
                return None
            
            # 使用第一个EPUB文件作为主文件
            main_epub = epub_files[0]
            file_path = os.path.join(folder_path, main_epub)
            
            # 从文件夹名和文件名提取信息
            book_info = self._extract_book_info_from_folder(folder_name, main_epub, file_path)
            
            # 添加章节信息
            if len(epub_files) > 1:
                book_info['chapters'] = [self._extract_chapter_info(chap, folder_path) 
                                       for chap in epub_files]
                book_info['total_chapters'] = len(epub_files)
            
            return book_info
            
        except Exception as e:
            logger.error(f"扫描书籍文件夹失败 {folder_path}: {e}")
            return None
    
    def _extract_book_info_from_folder(self, folder_name: str, epub_file: str, file_path: str) -> Dict:
        """
        从文件夹结构提取书籍信息
        
        Args:
            folder_name: 文件夹名（书名）
            epub_file: EPUB文件名
            file_path: 完整文件路径
            
        Returns:
            Dict: 书籍信息
        """
        try:
            file_size = os.path.getsize(file_path)
            
            # 从文件名提取章节信息
            chapter_name = epub_file.replace('.epub', '')
            
            # 从文件夹名提取书名和作者
            if ' - ' in folder_name:
                parts = folder_name.split(' - ')
                title = parts[0].strip()
                author = parts[1].strip() if len(parts) > 1 else "未知作者"
            else:
                title = folder_name
                author = "未知作者"
            
            return {
                'book_id': self._generate_book_id(title, author),
                'title': title,
                'author': author,
                'download_path': file_path,
                'file_size': file_size,
                'file_format': 'epub',
                'folder_structure': True,
                'chapter_name': chapter_name,
                'metadata_hash': self._generate_metadata_hash(title, author, file_size)
            }
            
        except Exception as e:
            logger.error(f"从文件夹提取书籍信息失败: {e}")
            return {}
    
    def _extract_chapter_info(self, chapter_file: str, folder_path: str) -> Dict:
        """
        提取章节信息
        
        Args:
            chapter_file: 章节文件名
            folder_path: 文件夹路径
            
        Returns:
            Dict: 章节信息
        """
        try:
            file_path = os.path.join(folder_path, chapter_file)
            file_size = os.path.getsize(file_path)
            chapter_name = chapter_file.replace('.epub', '')
            
            return {
                'chapter_name': chapter_name,
                'file_path': file_path,
                'file_size': file_size,
                'file_format': 'epub'
            }
            
        except Exception as e:
            logger.error(f"提取章节信息失败 {chapter_file}: {e}")
            return {}
    
    def _extract_book_info(self, file_path: str) -> Optional[Dict]:
        """
        从EPUB文件路径提取书籍信息
        支持多种文件名格式：
        1. "书名 - 作者.epub"
        2. "书名.epub"
        3. "[ID] 书名.epub"
        
        Args:
            file_path: EPUB文件路径
            
        Returns:
            Optional[Dict]: 书籍信息
        """
        try:
            file_name = os.path.basename(file_path)
            file_size = os.path.getsize(file_path)
            
            # 从文件名提取基本信息
            base_name = file_name.replace('.epub', '').strip()
            
            # 尝试提取书籍ID（如果文件名中包含ID）
            book_id = None
            title = base_name
            author = "未知作者"
            
            # 格式1: "[ID] 书名 - 作者"
            if base_name.startswith('[') and ']' in base_name:
                start = base_name.find('[')
                end = base_name.find(']')
                book_id = base_name[start+1:end]
                title_author_part = base_name[end+1:].strip()
                
                if ' - ' in title_author_part:
                    parts = title_author_part.split(' - ', 1)
                    title = parts[0].strip()
                    author = parts[1].strip() if len(parts) > 1 else "未知作者"
                else:
                    title = title_author_part
            
            # 格式2: "书名 - 作者"
            elif ' - ' in base_name:
                parts = base_name.split(' - ', 1)
                title = parts[0].strip()
                author = parts[1].strip() if len(parts) > 1 else "未知作者"
            
            # 格式3: 纯文件名
            else:
                title = base_name
            
            # 清理标题中的多余空格
            title = ' '.join(title.split())
            author = ' '.join(author.split())
            
            # 如果文件夹结构存在，检查是否为章节文件
            folder_path = os.path.dirname(file_path)
            if folder_path != self.download_dir:
                # 这是文件夹结构中的章节文件，跳过单独处理
                return None
            
            return {
                'book_id': book_id or self._generate_book_id(title, author),
                'title': title,
                'author': author,
                'download_path': file_path,
                'file_size': file_size,
                'file_format': 'epub',
                'folder_structure': False,
                'metadata_hash': self._generate_metadata_hash(title, author, file_size)
            }
            
        except Exception as e:
            logger.error(f"提取书籍信息失败 {file_path}: {e}")
            return None
    
    def _generate_book_id(self, title: str, author: str) -> str:
        """生成书籍ID"""
        import hashlib
        text = f"{title}_{author}".encode('utf-8')
        return hashlib.md5(text).hexdigest()[:10]
    
    def _generate_metadata_hash(self, title: str, author: str, file_size: int) -> str:
        """生成元数据哈希用于检测变更"""
        import hashlib
        text = f"{title}_{author}_{file_size}".encode('utf-8')
        return hashlib.md5(text).hexdigest()
    
    def add_existing_downloads(self) -> int:
        """
        将现有下载添加到数据库
        
        Returns:
            int: 成功添加的数量
        """
        books_found = self.scan_downloads()
        added_count = 0
        
        for book_info in books_found:
            if self.library_manager.add_book(book_info):
                added_count += 1
        
        logger.info(f"成功添加 {added_count} 本书籍到数据库")
        return added_count